 with work_order as  ( 
    select 
    waybill_no,                                         --单号
    collect_set(second_level_name) as second_type_name, --运单所有二级类型名称
    max(is_flas_sign) as is_flas_sign,                  --优先取签收未收到标签
    max(create_time) as work_create_time                --最新投诉时间
    from 
    (                         
        select 
        waybill_no,                         
        second_level_code ,
        second_level_name,
        1  as is_flas_sign,
        create_time
        from jms_dwd.dwd_project_work_order_base_zipper_dt  --平台工单(卖家投诉问题)
        where dt  between date_add('{{ execution_date  | cst_ds }}',-60) and  '{{ execution_date  | cst_ds }}'
              and work_order_source_id = 1  
              and date(create_time) >= date_add('{{ execution_date  | cst_ds }}',-60)
        union all 
        select 
        waybill_no,
        second_type_code as second_level_code,
        second_type_name as second_level_name,
        if(second_type_code = 2104,1,0) as is_flas_sign,  --是否签收未收到
        create_time
        from jms_dwd.dwd_work_order_base_zipper_dt        --普通工单
        where dt between date_add('{{ execution_date  | cst_ds }}',-60) and  '{{ execution_date  | cst_ds }}'
        and date(create_time) >= date_add('{{ execution_date  | cst_ds }}',-60)
    )t group by waybill_no
),
--退转信息
reback as (  
select
    waybill_no
    ,apply_type_code
    ,apply_time
from
    (
    select
    waybill_no
    ,apply_type_code
    ,apply_time
    ,row_number() over(partition by waybill_no order by apply_time desc,apply_type_code ) as rk  --优先退回件
    from jms_dwd.dwd_tab_reback_transfer_express_base
    where date(apply_time) between date_add('{{ execution_date  | cst_ds }}',-60) and  '{{ execution_date  | cst_ds }}'
    )t where rk=1
)

--collect表信息
, tab_collect_all as ( 
select
 waybill_no      --单号
,scan_time       --扫描时间
,scan_type       --扫描类型
,scan_site_code  --扫描网点
,store_code      --门店编码
,store_name      --门店名称
,scan_date       --扫描日期
,input_time      --录入时间
,input_date      --录入日期
from (
    select
    *,row_number() over(partition by waybill_no,scan_site_code,store_code,input_date,scan_type order by scan_time) as rk
    from
    (
    select
    waybill_no    --单号
    ,scan_time    --扫描时间
    ,scan_type    --扫描类型
    ,scan_site_code --扫描网点
    ,post_station_code as store_code --门店编码
    ,storage_address   as store_name--门店名称
    ,input_time
    ,date(scan_time)  as scan_date
    ,date(input_time) as input_date
    from  jms_dwd.dwd_tab_barscan_collect_base_dt
    where dt between date_add('{{ execution_date  | cst_ds }}',-60) and '{{ execution_date  | cst_ds }}'
          and length (waybill_no) <= 15
          and scan_site_code not like 'B%'
          and scan_site_code is not null
          and scan_type='出库扫描'
    )t 
    )t1 where rk>=2 and date(scan_time) between date_add('{{ execution_date  | cst_ds }}',-6) and '{{ execution_date  | cst_ds }}'
) 

insert  overwrite table jms_dm.dm_download_repeat_outscan_detail_dt partition(dt)
select
tt.waybill_no       --单号
,net.agent_code     --代理区code
,net.agent_name     --代理区name
,tt.scan_time       --扫描时间
,tt.scan_type       --扫描类型
,tt.scan_site_code  --扫描网点
,net.name           --扫描网点名称
,tt.store_code      --门店编码
,tt.store_name      --门店名称
,if(work_order.waybill_no  is not null,1,0) as is_work_order                  --是否客诉
,if(work_order.waybill_no  is not null and is_flas_sign=1,1,0) as is_flas_sign--是否虚假签收
,reback.apply_type_code                                                       --退转类型4退件5转寄
,if(sign_del.billcode is not null,1,0 ) as is_sign_del                        --是否签收删除
,reback.apply_time                                                            --退转申请时间
,date(tt.scan_time) as date_time  
,date(tt.scan_time) as dt                                                     --日期
from tab_collect_all tt 
left join jms_dim.dim_network_whole_massage net on net.code=tt.scan_site_code
left join work_order on work_order.waybill_no =tt.waybill_no and work_order.work_create_time>tt.scan_time
left join reback on reback.waybill_no =tt.waybill_no and reback.apply_time<tt.scan_time
left join 
(
    select billcode,scan_type,scan_time,scan_site_code
    from  jms_dwd.dwd_tab_barscan_sign_delete_dt 
    where dt between date_add('{{ execution_date  | cst_ds }}',-60) and  '{{ execution_date  | cst_ds }}' 
             and date(scan_time) between date_add('{{ execution_date  | cst_ds }}',-60) and  '{{ execution_date  | cst_ds }}'
             and scan_type='出库扫描'
) sign_del  on sign_del.billcode =tt.waybill_no and sign_del.scan_time=tt.scan_time
distribute by pmod(hash(rand()), 3);